alter table jms_dm.dm_mng_explosion_warehouse_network_dt
    set tblproperties ('external.table.purge' = 'true');
drop table jms_dm.dm_mng_explosion_warehouse_network_dt;
create external table jms_dm.dm_mng_explosion_warehouse_network_dt
(
    final_sign_network_code string comment '最终签收派件网点编码',
    final_sign_network_name string comment '最终签收派件网点编码',
    end_agent_code          string comment '末端网点所属代理区编码',
    end_agent_name          string comment '末端网点所属代理区名称',
    need_sign_count_1d      int comment '1天应签收',
    retention_count_1d      int comment '1天内滞留',
    need_sign_count_2d      int comment '2天应签收',
    retention_count_2d      int comment '2天内滞留',
    need_sign_count_3d      int comment '3天应签收',
    retention_count_3d      int comment '3天内滞留',
    need_sign_count_5d      int comment '5天应签收',
    retention_count_5d      int comment '5天内滞留',
    need_sign_count_7d      int comment '7天应签收',
    retention_count_7d      int comment '7天内滞留',
    scan_sum                int comment '时效签收量',
    staff_code              string comment '员工编码',
    staff_name              string comment '员工名称',
    position_name           string comment '岗位名称',
    is_warn                 int comment '是否预警,全为1',
    final_plan_sign_date    string comment '规划签收日期',
    agent_type              string comment '虚拟代理区,代理区',
    virt_code               string comment '虚拟代理区code',
    virt_name               string comment '虚拟代理区name'
) comment '网点滞留预警小汇总'
    partitioned by (dt string comment '规划签收日期')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_mng_explosion_warehouse_network_dt'
;



alter table jms_dm.dm_mng_explosion_warehouse_network_dt
    add columns (
        end_city_id string comment '末端网点所属城市id'
        , end_city_name string comment '末端网点所属城市名称'
        , district_code string comment '转单责任网点片区code'
        , district_desc string comment '转单责任网点片区desc'
        , district_id string comment '转单责任网点片区id'
        ) cascade;

CREATE EXTERNAL TABLE jms_dm.dm_sqs_abnormal_network_cnt_dt
(
    agent_code        string COMMENT '代理区或者虚拟大区编码',
    agent_name        string COMMENT '代理区或者虚拟大区名称',
    network_cnt       int COMMENT '网点数',
    before_networ_cnt int COMMENT '上一天网点数',
    incr_networ_cnt   int COMMENT '新增网点数',
    network_cnt_rt    decimal(16, 2) COMMENT '环比 *100后的',
    low_networ_cnt    int COMMENT '减少网点数',
    target_value      int COMMENT '目标值',
    over_value        int COMMENT '超出目标值',
    data_type         string COMMENT '统计类型 筛单 、爆仓',
    date_time         date COMMENT '统计时间',
    agent_type        string COMMENT '类型：虚拟区、代理区'
)
    partitioned by (dt string comment '时间分区')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_sqs_abnormal_network_cnt_dt'
;
alter table jms_dm.dm_mng_explosion_warehouse_network_dt
    add columns (
        duration string comment '60天内持续天数',
        area_code string comment '区域code',
        area_name string comment '区域名字'
        ) cascade;